IF OBJECT_ID('dbo.GetDepartments') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.GetDepartments
    IF OBJECT_ID('dbo.GetDepartments') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.GetDepartments >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.GetDepartments >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.GetDepartments
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 5/24/2007
--
-- Description: Returns list of Departments from AgrAgreement_fct table
--
-- Parameters:  None
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.GetDepartments()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 7/11/07     KC        Created
-- 8/15/07     KC        Added Security to limit results seen
-- 8/16/07     KC        Commented out Security for performance while testing
-- 8/17/07     KC        Security changed to Cached table
-- 9/13/07     KC        Changed to Info Table, and Org Table 
-- 9/17/07     KC        If Search by "ALL" don't return any rows
-- 12/11/08    CB        Added column for infoed searches, and ability to recive either Insight institution Id or 
--                       institution name (due to being used for Infoed reports as well)
-- 12/17/08    KC        Security changed to new function
-- 01/05/08    KC        Security Function Perf Issues: use TempTable
-- 01/07/08    KC        Roll back temp table change.
--
-- =======================================================================
CREATE PROCEDURE dbo.GetDepartments
     (@Institution                 varchar(200)=NULL ,
     @DomainUserId                 INT )
AS
BEGIN
     /*  Returned Fields for Dropdown
     --------------------------------
     DeptId              INT
     Department          VARCHAR
     DepartmentChiefCode VARCHAR
     ChiefCode          VARCHAR
     */

     -- Initial Settings
     SET NOCOUNT ON
          
     -- Declare Local Variables
     DECLARE   @Error            int,
               @RowCount         int,
               @InstitutionId    int

    --This is to handle that for Infoed you need to bind to Institution
     IF ISNUMERIC(@Institution) =0
     BEGIN
          SELECT @InstitutionId = Id 
            FROM FwkDomainOrganization 
           WHERE Institution =@Institution AND FwkDomainOrganizationTypeId =2
     END
     ELSE
     BEGIN
       SET @InstitutionId = CONVERT(int,@Institution)
     END

     
     -- Result Query    
     SELECT DISTINCT D.Id AS DeptId,
               D.Name AS Department,
               D.Name + ' (' + D.ChiefCode + ')' AS DepartmentChiefCode,
               D.ChiefCode
     FROM      dbo.AgrAgreement_Info AI INNER JOIN dbo.SvcEntity_FwkDomainOrganization_Map EDOM ON AI.SvcEntityId = EDOM.SvcEntityId 
               INNER JOIN dbo.FwkDomainOrganization U ON U.Id = EDOM.FwkDomainOrganizationId
               INNER JOIN dbo.FwkDomainOrganization D ON D.Id = U.DepartmentId 
               -- Security Issues: Not all Users can see all Agreements and therefore all Departments
               INNER JOIN dbo.fnGetSecurityAgreements(@DomainUserId) AUTH ON AI.AgreementId = AUTH.AgreementId
     WHERE     D.FwkDomainOrganizationTypeId =3 AND
               (@Institution IS NULL OR D.InstitutionId =@InstitutionId)
     ORDER BY D.Name               

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1

     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.GetDepartments') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.GetDepartments >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.GetDepartments >>>'
go

GRANT EXECUTE ON dbo.GetDepartments TO InsightReport,Insight
go

